﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace quanlycd3lop.Data_Access
{
    class AccessData_ThueDia
    {
        private SqlConnection con = new SqlConnection();
        public SqlConnection Getconn
        {
            get
            {
                return con;
            }
        }
        public AccessData_ThueDia()
        {
            con.ConnectionString = "Data Source=PC-PC\\SQLEXPRESS;Initial Catalog=quanlycd;Integrated Security=True";
        }
        public void Open()
        {
            if (con.State == ConnectionState.Closed)
                con.Open();
        }
        public void Close()
        {
            if (con.State == ConnectionState.Open)
                con.Close();
        }
        public DataTable Get_DataTable(String sql)
        {
            SqlDataAdapter adp = new SqlDataAdapter(sql, con);
            DataTable tb = new DataTable();
            adp.Fill(tb);
            return tb;
        }
        public DataSet Get_DataSet()
        {
            DataTable tb = new DataTable();
            DataSet ds = new DataSet();
            ds.Tables.Add(tb);
            return ds;
        }
        public void Excute_SQL(String sql)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        public DataTable Excute_ps(string name_ps, SqlParameter[] pr)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = name_ps;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Connection = con;
            cmd.Parameters.AddRange(pr);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataTable tb = new DataTable();
            adp.Fill(tb);
            return tb;

        }
        public DataTable Excute_ps(string name_ps, string MaCD, string MaKH, int SoLuongmuon,DateTime NgayMuon,DateTime Ngaytra)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = name_ps;
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p1 = new SqlParameter("@MaCD", MaCD);
            SqlParameter P2 = new SqlParameter("@MaKH", MaKH);
            SqlParameter p3 = new SqlParameter("@SoLuongmuon", SoLuongmuon);
            SqlParameter p4 = new SqlParameter("@NgayMuon", NgayMuon);
            SqlParameter p5 = new SqlParameter("@Ngaytra", Ngaytra);

            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(P2);
            cmd.Parameters.Add(p3);
            cmd.Parameters.Add(p4);
            cmd.Parameters.Add(p5);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataTable tb = new DataTable();
            adp.Fill(tb);
            return tb;
        }
        public void Update_DataSet(ref DataTable tb, String SQL)
        {
            SqlDataAdapter adp = new SqlDataAdapter(SQL, con);
            SqlCommandBuilder bd = new SqlCommandBuilder();
            bd.DataAdapter = adp;
            adp.Update(tb);
        }
    }
}
